﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace OCAP_FACAEmail_Api    //这个每次都要改成你当前项目的命名空间名字   namespace ok
{
    public class SqlHelper
    {
        string MsSqlConnet_APPDB = ConfigurationManager.AppSettings["MsSqlConnet_APPDB"];
        string MsSqlConnet_LogDB = ConfigurationManager.AppSettings["MsSqlConnet_LogDB"];
        public SqlConnection Connection
        {
            get;
            set;
        }
        

        public SqlHelper(int type = 1)
        {
            string connectionString = string.Empty;
            switch (type)
            {
                case 2:
                    connectionString = MsSqlConnet_APPDB;
                    break;
                case 3:
                    connectionString = MsSqlConnet_LogDB;
                    break;
                default:
                    connectionString = MsSqlConnet_APPDB;
                    break;
            }
            Connection = new SqlConnection(connectionString);
        }

        public object ExecuteScalar(string cmdString)
        {
            try
            {
                Connection.Open();
                SqlCommand cmd = new SqlCommand(cmdString, Connection);
                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (Connection.State != System.Data.ConnectionState.Closed)
                    Connection.Close();
            }
        }

        public int ExecuteNonQuery(string cmdString)
        {
            try
            {
                //var list = cmdString.Split(',').ToList();
                //for (int i = 0; i < list.Count; i++)
                //{
                //    bool isString = false;
                //    if (list[i].StartsWith("'") && list[i].EndsWith("'"))
                //    {
                //        isString = true;
                //        list[i] = list[i].Substring(1, list[i].Length - 2);
                //    }

                //    if (list[i].Contains("'"))
                //        list[i] = list[i].Replace("'", "''");

                //    if (isString)
                //        list[i] = "'" + list[i] + "'";
                //}

                //StringBuilder sb = new StringBuilder();
                //foreach (var s in list)
                //    sb.Append(s + ",");

                Connection.Open();
                var cmd = new SqlCommand(cmdString, Connection);

                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (Connection.State != System.Data.ConnectionState.Closed)
                    Connection.Close();
            }
        }

        public DataTable GetData(string cmdString)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cmdString, Connection);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    throw new Exception("没有找到表");
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
            }
        }

        public bool ConnectTest()
        {
            try
            {
                var o = ExecuteScalar("select GetDate()");
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}